package com.feasy.generator.managedb.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DataBaseUtils {

    /**
     * 加载驱动.
     */
    public static void loadDriver(String driverName) {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接.
     */
    public static Connection getConnection(String dbtype, String dbUrl, String username, String password) {
        try {
            Connection connection = null;
            if ("1".equals(dbtype)) {
                connection = DriverManager.getConnection(dbUrl, username, password);
            } else if ("2".equals(dbtype)) {
                Class.forName("oracle.jdbc.driver.OracleDriver");//加载数据驱动
                connection = DriverManager.getConnection(dbUrl, username, password);// 连接数据库
            }
            return connection;
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String getJdbcUrl(String dbType, String ip, String port, String dbname) {
        String jdbcUrl = "";
        if ("1".equals(dbType)) {
            jdbcUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?serverTimezone=UTC&characterEncoding=utf-8";
        } else if ("2".equals(dbType)) {
            if (StringUtils.isEmpty(dbname)) {
                jdbcUrl = "jdbc:oracle:thin:@" + ip + ":" + port + ":orcl";
            } else {
                jdbcUrl = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + dbname;
            }
        }
        return jdbcUrl;
    }

    public static String getTableNameListSql() {
        return "select table_name tableName, table_comment tableComment  from information_schema.tables where table_schema = (select database())";
    }

    public static List<Map<String, Object>> getTableNameList(Connection connection) {
        List<Map<String, Object>> tableNameList = new ArrayList<>();
        try {
            String sql = "select table_name tableName, table_comment tableComment  from information_schema.tables where table_schema = (select database())";
            tableNameList = queryMapList(connection, sql);

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableNameList;
    }

    public static List<Map<String, Object>> getTableNameListOracle(Connection connection, String username) {

        List<Map<String, Object>> list = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            DatabaseMetaData dbmd = connection.getMetaData();

            resultSet = dbmd.getTables(connection.getCatalog(), "%", null, new String[]{"TABLE"});
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                String tableComment = resultSet.getString("REMARKS");
                String user = resultSet.getString("TABLE_SCHEM");

                if (username.toUpperCase().equals(user.toUpperCase())) {
                    Map<String, Object> map = new HashMap<>();
                    map.put("tableName", tableName);
                    map.put("tableComment", tableComment);
                    list.add(map);
                }
                System.out.println("tableName=" + tableName + ",   tableComment=" + tableComment + ",    user=" + user);

            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    System.out.println("数据库连接关闭！");
                    connection.close();
                }
            } catch (SQLException e) {
                System.err.println(e.getMessage());
            }
        }
        return list;
    }



    public static String getTableComment(Connection connection, String tableName, String dbtype) {
        String tableComment = "";
        try {
            String sql ="";
            if ("1".equals(dbtype)) {
                sql = "select table_name tableName, table_comment TABLECOMMENT  from information_schema.tables where table_schema = (select database()) and table_name = \"" + tableName + "\"";
            } else if ("2".equals(dbtype)) {
                sql = "select table_name tableName,  comments tableComment from user_tab_comments where table_type='TABLE' and TABLE_NAME = '" + tableName +"'";
            }

            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                tableComment = rs.getString("TABLECOMMENT");
            }
            rs.close();
            pstmt.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableComment;
    }

    /**
     * 没有条件，若加条件即 添加 and table_name = "ceshitableName".
     */
    public static String getTableColumnSql(String dbtype, String tableName) {
        String sql = "";
        if ("1".equals(dbtype)) {
            sql = "select column_name columnName, data_type dataType, column_comment columnComment, column_key columnKey, extra from information_schema.COLUMNS where  table_schema = (select database()) and table_name = \"" + tableName +"\"";
        } else if ("2".equals(dbtype)) {
            sql = "select utc.column_name as \"columnName\",utc.data_type as \"dataType\",ucc.comments as \"columnComment\",' ' as \"columnKEY\" ,' ' as \"extra\"" +
                    " from user_tab_columns utc,user_col_comments ucc where  utc.table_name = ucc.table_name " +
                    " and utc.column_name = ucc.column_name and utc.table_name = '"+tableName +"'";
        }
        return sql;
    }

    /**
     * sql里面已经包含数据.
     */
    public static List<Map<String, Object>> queryMapList(Connection connection, String sql) {
        List<Map<String, Object>> list = new ArrayList<>();
        List<String> headers = new ArrayList<String>();
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData resultSetMetaData = rs.getMetaData();

            int columnCount = resultSetMetaData.getColumnCount();

            for (int i = 1; i <= columnCount; i++) {
                headers.add(resultSetMetaData.getColumnLabel(i));
            }

            while (rs.next()) {
                Map<String, Object> obj = new HashMap<String, Object>();
                for (String header : headers) {

                    obj.put(header, rs.getObject(header));
                }
                list.add(obj);
            }
            System.out.println(list);
            rs.close();
            pstmt.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}
